﻿using System;
using System.Collections.Generic;
using System.Windows.Forms;
using AccessControl.Business;
using DevExpress.XtraEditors;


namespace AccessControl.SecurityMonitoring.Form
{
    public partial class UpdateDatabase : XtraForm
    {

        public UpdateDatabase()
        {
            InitializeComponent();
        }

        private void btnUpdatePerson_Click(object sender, EventArgs e){

            btnUpdate.Enabled = false;

            ExecuteCommand(@"delete from tblPerson where PersonRefNumber in
(select personrefnumber from tblPerson group by PersonRefNumber having COUNT(*)>1)
 and not exists (select stcode from Amoozesh.self_service.dbo.users WHERE kart_etebar=pkperson)

UPDATE tblPerson SET pkPerson=PersonRefNumber WHERE pkPerson!=PersonRefNumber AND NOT EXISTS (SELECT kart_etebar from AMoozesh.self_service.dbo.users WHERE PersonRefNumber=stcode AND kart_etebar=pkPerson)
UPDATE tblPerson SET pkPerson=kart_etebar
		from tblPerson AC INNER JOIN AMoozesh.self_service.dbo.users on stcode=personrefnumber
	WHERE LEN(kart_etebar)>=10 and pkPerson!=kart_etebar
	and kart_etebar not in(select pkPerson from tblPerson)", lblUpdatePersons);

            ExecuteCommand(@"insert into tblPerson (pkPerson, PersonFirstName, PersonLastName, PersonPhoto, PersonTitle, PersonDesc, PersonIsMale, PersonInfoDate, PersonRefNumber, PersonExpireDate)
select CASE WHEN rtrim(ltrim(ISNULL(kart_etebar,'')))='' THEN Students.stcode ELSE kart_etebar END,
	Students.name ,Students.family , stu_pic , 1, '', case Students.sex when 1 then 1 else 0 end, getDate(), Students.stcode, null
from Amoozesh.Amozesh.dbo.fsf as students left join AMoozesh.self_service.dbo.users as ss on students.stcode =ss.stcode collate SQL_Latin1_General_CP1_CI_AS
left join AMoozesh.Amozpic.dbo.stuImage pic on pic.stcode collate SQL_Latin1_General_CP1_CI_AS=Students.stcode 
where NOT EXISTS (SELECT pkPerson from tblPerson AC where AC.PersonRefNumber=Students.stcode OR AC.pkPerson=Students.stcode)
and (idvazkol=1 OR idvazkol=2 OR LEN(kart_etebar)>=10)", lblAddPerson);

            ExecuteCommand(@"update tblPerson Set PersonPhoto=stu_pic
from tblPerson inner join Amoozesh.Amozpic.dbo.stuimage on stcode collate SQL_Latin1_General_CP1_CI_AS=personrefnumber 
where PersonPhoto is null", lblUpdatePics);

            ExecuteCommand(@"insert into tblPersonGroup (fkPerson,fkGroup,fkGroupType)
select pkPerson,idresh,1
from tblPerson inner join Amoozesh.Amozesh.dbo.fsf on PersonRefNumber=stcode
INNER JOIN Amoozesh.Amozesh.dbo.fresh on id=idresh
WHERE NOT EXISTS(SELECT top 1 * FROM tblPersonGroup PG where PG.fkPerson=pkPerson AND fkGroupType=1)", lblAddReshteh);

            ExecuteCommand(@"UPDATE tblPersonGroup Set fkGroup=idresh
FROM tblPersonGroup INNER JOIN tblPerson ON pkPerson=fkPerson
INNER JOIN Amoozesh.Amozesh.dbo.fsf on PersonRefNumber=stcode
WHERE fkGroupType=1 and fkGroup!=idresh", lblUpdateReshteh);

            ExecuteCommand(@"insert into tblPersonGroup (fkPerson,fkGroup,fkGroupType)
select distinct pkPerson,id_khab,2
from tblPerson inner join Amoozesh.Amozesh.dbo.fsabt_khabgah on PersonRefNumber=stcode and vazfali=1
WHERE NOT EXISTS(SELECT top 1 * FROM tblPersonGroup PG where PG.fkPerson=pkPerson AND fkGroupType=2)", lblAddDormitory);

            ExecuteCommand(@"UPDATE tblPersonGroup Set fkGroup=id_khab
FROM tblPersonGroup INNER JOIN tblPerson ON pkPerson=fkPerson
INNER JOIN Amoozesh.Amozesh.dbo.fsabt_khabgah on PersonRefNumber=stcode and vazfali=1
WHERE fkGroupType=2 and fkGroup!=id_khab", lblUpdateDormitory);


            ExecuteCommand(@"insert into tblPersonGroup (fkPerson,fkGroup,fkGroupType)
select distinct pkPerson,id_khab*1000+num_otagh,3
from tblPerson inner join Amoozesh.Amozesh.dbo.fsabt_khabgah on PersonRefNumber=stcode and vazfali=1
WHERE NOT EXISTS(SELECT top 1 * FROM tblPersonGroup PG where PG.fkPerson=pkPerson AND fkGroupType=3)", lblAddRooms);

            ExecuteCommand(@"UPDATE tblPersonGroup Set fkGroup=id_khab*1000+num_otagh
FROM tblPersonGroup INNER JOIN tblPerson ON pkPerson=fkPerson
INNER JOIN Amoozesh.Amozesh.dbo.fsabt_khabgah on PersonRefNumber=stcode and vazfali=1
WHERE fkGroupType=3 and fkGroup!=id_khab*1000+num_otagh", lblUpdateRooms);


            btnUpdate.Enabled = true;
        }

        

        private void ExecuteCommand(string command,Label resultLabel){
            string message;
            resultLabel.Text = "در حال به روز رسانی";
            
            resultLabel.Refresh();
            Refresh();
            
            var rowsAffected = UpdateBusiness.RunUpdateCommand(command + Environment.NewLine + "SELECT @@ROWCOUNT", out message);
            if (message == null)
                resultLabel.Text = rowsAffected + " رکورد";
            else
                resultLabel.Text = "خطا";
            Tip.SetToolTip(resultLabel, message);

            resultLabel.Refresh();
            Refresh();

        }

    }
}